package com.ysh.mybatisdemo.mapper;

import com.ysh.mybatisdemo.model.UserInfo;
import org.apache.ibatis.annotations.*;

import java.util.List;

@Mapper
public interface UserInfoMapper {

    /*@Results(id = "BaseMap",value ={
            @Result(column = "delete_flag",property = "deleteFlag"),
            @Result(column = "create_time",property = "createTime"),
            @Result(column = "update_time",property = "updateTime"),

    })*/
    @Select("select * from user_info")
    List<UserInfo> selectAll();

    @Select("select id,username,password,age,gender,phone,delete_flag " +
            "as deleteFalg, create_time as createTime,update_time as updateTime from user_info")//比较麻烦
    List<UserInfo> selectAll2();

    /*@ResultMap(value = "BaseMap")*/
    @Select("select * from user_info where id = #{id}")
    UserInfo selectById(Integer id);

    @Select("select * from user_info where id = #{id}")
//只有一个参数时直接匹配
    UserInfo selectById2(Integer id111);

    @Select("select * from user_info where username = #{aaa} and password = #{bbb}")
//两个或多个时必须保证参数名字相同或者用param默认名称
    List<UserInfo> selectByUsernameAndPassword(String aaa, String bbb);

    @Select("select * from user_info where username = #{param2} and password = #{param1}")//不建议使用param
    List<UserInfo> selectByUsernameAndPassword2(String password, String username);

    @Select("select * from user_info where username = #{username1} and password = #{password1}")
    List<UserInfo> selectByUsernameAndPassword3(@Param("password1") String password, @Param("username1") String username);

    @Select("select * from user_info where username = '${username}' and password = '${password}'")
    List<UserInfo> selectByUsernameAndPassword4( String password, String username);

    @Options(useGeneratedKeys = true,keyProperty = "id")
    @Insert("insert into user_info (username, password, age) VALUES (#{username}, #{password}, #{age})")
    Integer insertUser(UserInfo userInfo);

    @Insert("insert into user_info (username, password, age) " +
            "VALUES (#{userInfo.username}, #{userInfo.password}, #{userInfo.age})")
    Integer insertUser2(@Param("userInfo") UserInfo userInfo);

    @Delete("delete from user_info where id = #{id}")
    Integer deleteUser(Integer id);

    @Update("update user_info set delete_flag = #{deleteFlag},phone = #{phone} where id = #{id}")
    Integer updateUser(UserInfo userInfo);

    @Insert("insert into user_info (username, password, age,delete_flag) " +
            "VALUES (#{username}, #{password}, #{age},#{deleteFlag})")
    Integer insertUser3(UserInfo userInfo);

    @Select("select * from user_info order by id ${order}")
    List<UserInfo> selectByOrder(String order);

    @Select("select * from user_info where username like CONCAT('%',#{userName},'%')")
    List<UserInfo> selectByLike(String userName);

    //注解的动态sql
    @Insert("<script>" +
            "insert into user_info" +
            "<trim prefixOverrides=\",\" suffixOverrides=\",\" prefix=\"(\" suffix=\")\">" +
            "<if test='username!=null'> username, </if>" +
            "<if test='password!=null'> `password`, </if>" +
            "<if test='age!=null'> age, </if>" +
            "<if test='gender!=null'> gender, </if>" +
            "<if test='phone!=null'>phone, </if>" +
            "</trim> VALUES " +
            "<trim suffixOverrides=\",\" prefixOverrides=\",\" prefix=\"(\" suffix=\")\" >" +
            "<if test='username!=null'> #{username}, </if>" +
            "<if test='password!=null'> #{password}, </if>" +
            "<if test='age!=null'> #{age}, </if>" +
            "<if test='gender!=null'> #{gender}, </if>" +
            "<if test='phone!=null'> #{phone}, </if>" +
            "</trim>" +
            "</script>")
    Integer insertUser4(UserInfo userInfo);
}
